package studentmanager;

import gui.CustomJButton;
import gui.GUIMain;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.InputEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Random;

import javax.swing.AbstractAction;
import javax.swing.BorderFactory;
import javax.swing.JFileChooser;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPopupMenu;
import javax.swing.ListSelectionModel;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.filechooser.FileNameExtensionFilter;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import structure.Course;
import structure.Student;

public class GUIStudentManagerLeft extends JPanel{

	private static final long serialVersionUID = 1L;
	private GUIStudentManager parent;
	private GUICourseDisplay display;
	private CustomJButton addCourseButton;
	private JPopupMenu menu;
	private int currentClickedRow;
	private Course course;
	private ArrayList<Student> studentArray;
	
	public GUIStudentManagerLeft(GUIStudentManager parent) {
		this.parent = parent;
		setOpaque(false);
		setLayout(new BorderLayout());
		setBorder(BorderFactory.createEmptyBorder(0, 10, 0, 10));
		display = new GUICourseDisplay();
		createPopupMenu();
		
		JPanel bottomPanel = new JPanel(new FlowLayout(FlowLayout.CENTER));
		bottomPanel.setOpaque(false);
		addCourseButton = new CustomJButton("Thêm", 100, 30, Color.WHITE, CustomJButton.DARKER,
				Color.darkGray, 2, false, true);
		addCourseButton.addActionListener(new AddCourse());
		bottomPanel.add(addCourseButton);
		add(bottomPanel,BorderLayout.SOUTH);
		
		display.getJList().setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
		display.getJList().addListSelectionListener(new CourseSelectionListener());
		display.getJList().addMouseListener(new CourseMouseListener());
		add(display, BorderLayout.CENTER);
		refresh();
	}
	
	public void refresh(){
		display.getCourseFromDB(true);
		if(display.getJList().getModel().getSize() == 0) return;
	}
	
	private void createPopupMenu(){
		menu  = new JPopupMenu();
		JMenuItem menuItem;
		
		menuItem = new JMenuItem("Chỉnh sửa");
		menuItem.addActionListener(new EditCourse());
		menu.add(menuItem);
		
		menuItem = new JMenuItem("Xuất excel");
		menuItem.addActionListener(new ExportCourse());
		menu.add(menuItem);
		
		menuItem = new JMenuItem("Xoá");
		menuItem.addActionListener(new DeleteCourse());
		menu.add(menuItem);
	}
	
	private String getACellValues(Cell cell){
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                String temp = df.format(cell.getDateCellValue());
                return temp;
            } else {
                String str = new DecimalFormat("#").format(cell.getNumericCellValue());
                return str;
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        default:
			return "";
		}
	}
	
	private void readExcel(File file) throws IOException, InvalidFormatException{
		Course course = new Course();
		course.setCourseID(-1);
		ArrayList<Student> studentArray = new ArrayList<>();
		Workbook workbook = WorkbookFactory.create(file);
		
		Sheet sheet = workbook.getSheetAt(0);

		int numofRow = 0;
		for (Row row : sheet) {
			int numofCell = 0;
			for (Cell cell : row) {
				if(numofCell == 0){
					numofCell++;
					continue;
				}else if(numofCell == 1){
					if(numofRow == 0){
						course.setCourseTitle(getACellValues(cell));
					} else if(numofRow == 1){
						course.setDateOfExam(getACellValues(cell));
					} else if(numofRow == 2){
						course.setTimeOfExam(getACellValues(cell));
					} else if(numofRow == 3){
						course.setRoom(getACellValues(cell));
					}
					numofCell++;
				}else{
					break;
				}
			}
			numofRow++;
			if(numofRow >= 4){
				break;
			}
		}
		sheet = workbook.getSheetAt(1);
		numofRow = 0;
		for (Row row : sheet) {
			if(numofRow==0){
				numofRow++;
				continue;
			}
			Student temp = new Student();
			
			
			
			int numofCell = 0;
			for (Cell cell : row) {
				if(numofCell == 0){
					temp.setMSTS(getACellValues(cell));
				}else
				if(numofCell == 1){
					temp.setFirstName(getACellValues(cell));
				}else
				if(numofCell == 2){
					temp.setLastName(getACellValues(cell));
				}else
				if(numofCell == 3){
					temp.setDateOfBirth(getACellValues(cell));
				}else
				if(numofCell == 4){
					temp.setCMND(getACellValues(cell));
					try{
						String str = RandomStr(6, true);
						if(GUIMain.getDb().checkUserName(str) == 0){
							temp.setUsername(str);
						}
						str = RandomStr(8, true);
						temp.setPassword(str);
					}
					catch (SQLException ex)
					{
						return;
					}
					
				}else{
					break;
				}
				numofCell++;
			}
			studentArray.add(temp);
		}
		
		this.course = course;
		this.studentArray = studentArray;
		
	}
	
	private String RandomStr(int size, boolean lowerCase)
    {
        StringBuilder sb = new StringBuilder();
        char c[];
        Random rand = new Random();
        
        for(int i = 0; i<size; i++)
        {
        	c= Character.toChars(rand.nextInt(22) + 65);
            sb.append(c[0]);
        }
        if(lowerCase)
        {
            return sb.toString().toLowerCase();
        }
        return sb.toString();
    }
	
	private void addNewCourse(){
		
		GUIMain.getDb().InsertCourse(course.getCourseTitle(),
				course.getDateOfExam(),course.getTimeOfExam(), course.getRoom(), false);
		ArrayList<Course> current;
		try {
			current = GUIMain.getDb().GetLastestNonStuCourse();
			for(Course count : current){
				if(count.getCourseTitle().equals(course.getCourseTitle()) 
						&& count.getDateOfExam().equals(course.getDateOfExam())
						&& count.getTimeOfExam().equals(course.getTimeOfExam())
						&& count.getRoom().equals(course.getRoom())){
					course.setCourseID(count.getCourseID());
					
					for(Student stuCount : studentArray){
						GUIMain.getDb().InsertStudent(stuCount.getMSTS(), 
								stuCount.getFirstName(), 
								stuCount.getLastName(), 
								stuCount.getDateOfBirth(), 
								stuCount.getCMND(), course.getCourseID(),
								stuCount.getUsername(),stuCount.getPassword());
					}
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
	
	class EditCourse extends AbstractAction{

		private static final long serialVersionUID = 7756701945668432092L;

		@Override
		public void actionPerformed(ActionEvent arg0) {
			try{
				int curID = display.getJList().getModel().getElementAt(currentClickedRow).getCourseID();
				Course curCourse = GUIMain.getDb().getCourseByCourseID(curID);
				GUICourseEdit ex = new GUICourseEdit(parent.getFrame(), curCourse);
				ex.addWindowListener(new JDialogWindowListener());
				ex.pack();
				ex.setSize(300, ex.getSize().height);
				ex.setLocationRelativeTo(parent);
				ex.setVisible(true);
			}catch (SQLException e){
				e.getStackTrace();
			}
			
		}
		
	}
	
	class JDialogWindowListener extends WindowAdapter{

		@Override
		public void windowClosing(WindowEvent arg0) {
			super.windowClosing(arg0);
			refresh();
		}

		@Override
		public void windowClosed(WindowEvent e) {
			super.windowClosed(e);
			refresh();
		}	
		
		
	}
	
	class ExportCourse extends AbstractAction{

		private static final long serialVersionUID = 1L;

		@Override
		public void actionPerformed(ActionEvent arg0) {
			try{
				JFileChooser chooser = new JFileChooser();
				chooser.setCurrentDirectory(new java.io.File("."));
				chooser.setDialogTitle("Tạo tập tin excel");
				chooser.setFileFilter(new FileNameExtensionFilter("Tập tin excel...","xls", "xlsx"));
				chooser.setAcceptAllFileFilterUsed(false);

				if (chooser.showSaveDialog(parent) == JFileChooser.APPROVE_OPTION) {
					FileOutputStream out;
					String[] str = chooser.getSelectedFile().toString().split("\\.");
					
					if(str.length > 1){
						if(str[str.length - 1].equals("xls")|| str[str.length - 1].equals("xlsx")){
							out = new FileOutputStream(chooser.getSelectedFile().toString());
						}
						else{
							out = new FileOutputStream(chooser.getSelectedFile().toString() + ".xls");
						}
					}
					else{
						out = new FileOutputStream(chooser.getSelectedFile().toString() + ".xls");
					}
					
					Workbook wb = new HSSFWorkbook();
					Sheet s = wb.createSheet();
					Row r = null;
					Cell c = null;
					
					CellStyle cs = wb.createCellStyle();
					CellStyle cs2 = wb.createCellStyle();				
					Font f = wb.createFont();
					Font f2 = wb.createFont();

					f.setFontHeightInPoints((short) 10);
					f.setColor( (short)0xc );

					f2.setFontHeightInPoints((short) 10);
					f2.setColor( (short)Font.COLOR_RED );
					f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
					
					cs.setFont(f);
					cs.setBorderBottom(CellStyle.BORDER_THIN);
					cs.setBorderTop(CellStyle.BORDER_THIN);
					cs.setBorderLeft(CellStyle.BORDER_THIN);
					cs.setBorderRight(CellStyle.BORDER_THIN);
					cs.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);

					cs2.setBorderBottom(CellStyle.BORDER_MEDIUM);
					cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					cs2.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
					cs2.setBorderLeft(CellStyle.BORDER_MEDIUM);
					cs2.setBorderTop(CellStyle.BORDER_MEDIUM);
					cs2.setBorderRight(CellStyle.BORDER_MEDIUM);
					cs2.setAlignment(CellStyle.ALIGN_CENTER);
					
					cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
					cs2.setFont(f2);
					
					int curID = display.getJList().getModel().getElementAt(currentClickedRow).getCourseID();
					ArrayList<Student>  arrStu = GUIMain.getDb().getStudentsByCourse(curID);
						
					r = s.createRow(0);
					
			        c = r.createCell(0);
			        c.setCellValue("MSTS");
			        c.setCellStyle(cs2);
			        
			        c = r.createCell(1);
			        c.setCellValue("FirstName");
			        c.setCellStyle(cs2);
			        
			        c = r.createCell(2);
			        c.setCellValue("LastName");
			        c.setCellStyle(cs2);
			        
			        c = r.createCell(3);
			        c.setCellValue("CMND");
			        c.setCellStyle(cs2);
			        
			        c = r.createCell(4);
			        c.setCellValue("Math");
			        c.setCellStyle(cs2);
			        
			        s.setColumnWidth(0, 5000);
			        s.setColumnWidth(1, 10000);
			        s.setColumnWidth(2, 4000);
			        s.setColumnWidth(3, 5000);
			        s.setColumnWidth(4, 3000);
			        
					for (int rownum = (short) 1; rownum < arrStu.size() + 1; rownum++)
					{
					    r = s.createRow(rownum);
					    
				        c = r.createCell(0);
				        c.setCellValue(arrStu.get(rownum - 1).getMSTS());
				        c.setCellStyle(cs);
				        
				        c = r.createCell(1);
				        c.setCellValue(arrStu.get(rownum - 1).getFirstName());
				        c.setCellStyle(cs);
				        
				        c = r.createCell(2);
				        c.setCellValue(arrStu.get(rownum - 1).getLastName());
				        c.setCellStyle(cs);
				        
				        c = r.createCell(3);
				        c.setCellValue(arrStu.get(rownum - 1).getCMND());
				        c.setCellStyle(cs);
				        
				        c = r.createCell(4);
				        c.setCellValue(GUIMain.getDb().getMarkofStudent(arrStu.get(rownum - 1).getiD()));
				        c.setCellStyle(cs);
					}
					
					
					wb.write(out);
					out.close();
				}
				
				
				
				
				
			}catch (FileNotFoundException ex){
				ex.getStackTrace();
			}catch (IOException ex){
				ex.getStackTrace();
			
			} catch (SQLException ex) {
				ex.getStackTrace();
			}
		}
	}
	
	class AddCourse extends AbstractAction{
		
		private static final long serialVersionUID = 1L;

		@Override
		public void actionPerformed(ActionEvent arg0) {
			JFileChooser chooser = new JFileChooser();
			chooser.setCurrentDirectory(new java.io.File("."));
			chooser.setDialogTitle("Chọn tệp tin excel");
			chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
			chooser.setFileFilter(new FileNameExtensionFilter("Tập tin excel...","xls", "xlsx"));
			chooser.setAcceptAllFileFilterUsed(false);

			if (chooser.showOpenDialog(parent) == JFileChooser.APPROVE_OPTION) {
				try {
					readExcel(chooser.getSelectedFile());
					addNewCourse();
					refresh();
				} catch (IOException e) {
					JOptionPane.showMessageDialog(parent,
							"Có lỗi xảy ra trong quá trình đọc tập tin.\n" + e.getMessage(), 
							"Thông báo", JOptionPane.ERROR_MESSAGE);
				} catch (InvalidFormatException e) {
					JOptionPane.showMessageDialog(parent,
							"Có lỗi xảy ra trong quá trình đọc tập tin.\n" + e.getMessage(), 
							"Thông báo", JOptionPane.ERROR_MESSAGE);
				}
			}
		}
	}
	
	class DeleteCourse extends AbstractAction{

		private static final long serialVersionUID = 1L;

		@Override
		public void actionPerformed(ActionEvent arg0) {
			Object[] options = {"Đồng ý", "Thoát"};
			String sub = "\nChú ý: Xoá các Khoá thi đồng nghĩa với việc xoá các danh sách thí sinh \ntrong khoá cùng điểm, các câu trả lời của họ. Bạn muốn tiếp tục?";
			Course currentCourse = display.getJList().getModel().getElementAt(currentClickedRow);
			int rs = JOptionPane.showOptionDialog(parent, "Bạn có thực sự muốn hoá khoá thi " + currentCourse.getCourseTitle() + sub, 
					"Thông báo", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE,
					null, options, options[0]);
			if(rs == JOptionPane.YES_OPTION){
				try {
					ArrayList<Student> temp = GUIMain.getDb().getStudentsByCourse(currentCourse.getCourseID());
					for(Student stuCount : temp){
						GUIMain.getDb().DeleteStudentLogByStudent(stuCount.getiD());
						GUIMain.getDb().DeleteStudent(stuCount.getiD());
					}
					GUIMain.getDb().DeleteCourse(currentCourse.getCourseID());
					JOptionPane.showMessageDialog(parent, "Xoá Khoá thi thành công",
							"Thông báo", JOptionPane.INFORMATION_MESSAGE);
					refresh();
					display.getJList().clearSelection();
					parent.getRightPanel().refresh(null);
				} catch (SQLException e) {
					e.printStackTrace();
					JOptionPane.showMessageDialog(parent, 
							"Có lỗi trong quá trình xoá khoá thi./n" + e.getMessage(), 
							"Thông báo", JOptionPane.ERROR_MESSAGE);
				}
			}
		}
	}
	
	class CourseMouseListener extends MouseAdapter{

		@Override
		public void mouseClicked(MouseEvent e) {
			switch(e.getModifiers()) {
				case InputEvent.BUTTON1_MASK: {  
					super.mouseClicked(e);	
					break;
		        }
				case InputEvent.BUTTON2_MASK: {    
					break;
		        }
				case InputEvent.BUTTON3_MASK: {
					int temp = display.getJList().locationToIndex(e.getPoint());
					if(temp == -1) return;
			        currentClickedRow = temp;
			        menu.show(e.getComponent(), e.getX(), e.getY());
			        break;
		        }
			}
		}
	}
	
	class CourseSelectionListener implements ListSelectionListener{
		
		@Override
		public void valueChanged(ListSelectionEvent e) {
			if(!e.getValueIsAdjusting()){
				int ex = display.getJList().getSelectedIndex();
				if(ex==-1){
					parent.getRightPanel().refresh( null);
					parent.getRightPanel().setAddCourseButtons(false);
				}else{
					Course course = display.getJList().getSelectedValue();
					parent.getRightPanel().refresh(course);
					parent.getRightPanel().setAddCourseButtons(true);
				}
				
			}
		}
	}
	
}
